package com.agmte.api.common.crud.tableAutoCreate;

import com.agmte.api.common.crud.annotation.FieldAnno;
import com.agmte.api.common.crud.annotation.FieldId;
import com.agmte.api.common.crud.annotation.TableAnno;
import lombok.extern.slf4j.Slf4j;
import org.apache.logging.log4j.util.Strings;

import java.lang.reflect.Field;
import java.sql.*;
import java.util.*;
import java.util.stream.Collectors;

@Slf4j
public class PgTableSynchronizer {
    private final Connection connection;

    // Java 类型到 PostgreSQL 类型的映射
    private static final Map<Class<?>, String> TYPE_MAPPING = new HashMap<>();

    private Set<String> tableNames = new HashSet<>();

    protected static List<TableInfo> tableInfos = new ArrayList<>();

    static {
        TYPE_MAPPING.put(Long.class, "BIGINT");
        TYPE_MAPPING.put(long.class, "BIGINT");
        TYPE_MAPPING.put(Integer.class, "INTEGER");
        TYPE_MAPPING.put(int.class, "INTEGER");
        TYPE_MAPPING.put(String.class, "VARCHAR");
        TYPE_MAPPING.put(Boolean.class, "BOOLEAN");
        TYPE_MAPPING.put(boolean.class, "BOOLEAN");
        TYPE_MAPPING.put(Double.class, "DOUBLE PRECISION");
        TYPE_MAPPING.put(double.class, "DOUBLE PRECISION");
        TYPE_MAPPING.put(Float.class, "REAL");
        TYPE_MAPPING.put(float.class, "REAL");
        TYPE_MAPPING.put(java.util.Date.class, "TIMESTAMP");
        TYPE_MAPPING.put(java.sql.Date.class, "DATE");
        TYPE_MAPPING.put(java.sql.Timestamp.class, "TIMESTAMP");
    }


    public static void main(String[] args) {
        // 获取数据库连接
        try (Connection conn = DriverManager.getConnection(
                "jdbc:postgresql://localhost:5433/farmland_db",
                "postgres",
                "postgres")) {

            // 创建同步器实例
            PgTableSynchronizer synchronizer = new PgTableSynchronizer(conn, "com.agmte.api");

            synchronizer.syncTable();

            System.out.println();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }

    public PgTableSynchronizer(Connection connection, String packageName) {
        this.connection = connection;
        this.loadTable(packageName);
    }


    public void loadTable(String packageName) {
        Set<Class<?>> annotatedClasses = AnnotationScanner.findAnnotatedClassesV2(
                packageName, TableAnno.class);
        // 输出结果
        for (Class<?> clazz : annotatedClasses) {
            TableAnno annotation = clazz.getAnnotation(TableAnno.class);
            String tableName = annotation.value().isEmpty() ? PgTableSynchronizer.camelToSnake(clazz.getSimpleName()) : annotation.value();
            if (!tableNames.add(tableName)) {
                throw new RuntimeException("重复创建表");
            }
            TableInfo tableInfo = new TableInfo(tableName, annotation.describe(), clazz);

            Field[] declaredFields = clazz.getDeclaredFields();
            List<FieldInfo> findFields = new ArrayList<>();
            for (Field field : declaredFields) {
                Set<String> fieldNames = new HashSet<>();

                FieldInfo fieldInfo = new FieldInfo();
                fieldInfo.setName(field.getName());
                fieldInfo.setField(field);
                fieldInfo.setFieldType(field.getType());

                FieldId fieldId = field.getAnnotation(FieldId.class);
                FieldAnno fieldAnno = field.getAnnotation(FieldAnno.class);
                if (fieldId != null) {
                    String name = fieldId.value().isEmpty() ? field.getName() : fieldId.value();
                    fieldInfo.setFieldName(camelToSnake(name));
                    if (!fieldNames.add(fieldInfo.getFieldName())) {
                        throw new RuntimeException(tableName + "重复字段" + fieldInfo.getFieldName());
                    }
                    fieldInfo.setKey(true);

                    findFields.add(fieldInfo);

                    tableInfo.setKey(fieldInfo);

                } else if (fieldAnno != null) {
                    String name = fieldAnno.value().isEmpty() ? field.getName() : fieldAnno.value();
                    fieldInfo.setFieldName(camelToSnake(name));
                    if (!fieldNames.add(fieldInfo.getFieldName())) {
                        throw new RuntimeException(tableName + "重复字段" + fieldInfo.getFieldName());
                    }
                    fieldInfo.setDesc(fieldAnno.desc());
                    fieldInfo.setTypeHandler(fieldAnno.typeHandler());

                    findFields.add(fieldInfo);
                }
            }
            tableInfo.setFieldInfos(findFields);
            tableInfos.add(tableInfo);
        }


    }

    /**
     * 同步表结构
     */
    public void syncTable() throws SQLException {
        for (TableInfo tableInfo : tableInfos) {
            String tableName = tableInfo.getName();
            if (!tableExists(tableName)) {
                createTable(tableInfo);
            } else {
                updateTable(tableInfo);
            }
        }
    }

    /**
     * 判断表是否存在
     */
    private boolean tableExists(String tableName) throws SQLException {
//        String sql = "SELECT to_regclass(?)";
        String sql = "SELECT table_name\n" +
                "FROM information_schema.tables\n" +
                "WHERE table_schema = 'public'\n" +
                "  AND table_name = ?;";
        try (PreparedStatement stmt = connection.prepareStatement(sql)) {
            stmt.setString(1, tableName);
            try (ResultSet rs = stmt.executeQuery()) {
                if (rs.next()) {
                    return rs.getString(1) != null;
                }
            }
        }
        return false;
    }


    /**
     * 获取表的所有列名
     */
    private Set<String> getTableColumns(String tableName) {
        Set<String> columns = new HashSet<>();
        String sql = "SELECT column_name FROM information_schema.columns WHERE table_name = ?";

        try {
            PreparedStatement stmt = connection.prepareStatement(sql);
            stmt.setString(1, tableName);
            try (ResultSet rs = stmt.executeQuery()) {
                while (rs.next()) {
                    columns.add(rs.getString(1));
                }
            }
        } catch (Exception e) {
            throw new RuntimeException("获取表字段信息失败", e);
        }
        return columns;
    }


    /**
     * 生成创建表的SQL
     */
    private void createTable(TableInfo tableInfo) {
        if (tableInfo.getFieldInfos().isEmpty()) {
            return;
        }
        StringBuilder sql = new StringBuilder();

        String tableName = tableInfo.getName();
        String describe = Strings.isBlank(tableInfo.getDescribe()) ? tableName : tableInfo.getDescribe();


        sql.append("CREATE TABLE ").append(tableName).append(" (\n");
        List<String> fs = new ArrayList<>();
        List<String> fd = new ArrayList<>();

        for (FieldInfo f : tableInfo.getFieldInfos()) {

            String columnType = TYPE_MAPPING.getOrDefault(f.getFieldType(), "TEXT");


            if (f.isKey()) {
                fs.add(String.format(" %s bigserial PRIMARY KEY", f.getFieldName()));
                fd.add(String.format(" COMMENT ON COLUMN %s.%s IS '唯一Id' ", tableName, f.getFieldName()));
            } else {
                fs.add(String.format(" %s %s", f.getFieldName(), columnType));
                String desc = Strings.isBlank(f.getDesc()) ? f.getName() : f.getDesc();
                fd.add(String.format(" COMMENT ON COLUMN %s.%s IS '%s' ", tableName, f.getFieldName(), desc));
            }

        }

        sql.append(fs.stream().collect(Collectors.joining(",\n")) + "\n");

        sql.append(");\n\n");
        sql.append("COMMENT ON TABLE ").append(tableName).append(" IS '").append(describe).append("';\n");
        sql.append(fd.stream().collect(Collectors.joining(";\n")));

        try (Statement stmt = connection.createStatement()) {
            stmt.execute(sql.toString());
            log.info("新增表 {} --> {}", tableName, sql.toString().replace("\n", ""));
        } catch (SQLException e) {
            throw new RuntimeException("创建表失败,\n" + sql, e);
        }
    }

    private void updateTable(TableInfo tableInfo) {
        if (tableInfo.getFieldInfos().isEmpty()) {
            return;
        }
        String tableName = tableInfo.getName();

        Set<String> dbColumns = getTableColumns(tableName);
        for (FieldInfo f : tableInfo.getFieldInfos()) {
            if (!dbColumns.contains(f.getFieldName())) {

                String columnType = TYPE_MAPPING.getOrDefault(f.getFieldType(), "TEXT");

                String sql = String.format("ALTER TABLE %s ADD COLUMN %s %s;\n",
                        tableName, f.getFieldName(), columnType);

                String desc = Strings.isBlank(f.getDesc()) ? f.getName() : f.getDesc();
                // 添加字段注释
                sql = sql + String.format("COMMENT ON COLUMN %s.%s IS '%s'",
                        tableName, f.getFieldName(), desc);

                try (Statement stmt = connection.createStatement()) {
                    log.info("新增表 {} 字段 {} --> {}", f.getFieldName(), f.getFieldName(), sql.replace("\n", ""));
                    stmt.execute(sql);
                } catch (SQLException e) {
                    throw new RuntimeException("新增表字段失败,\n" + sql, e);
                }
            }
        }
    }

    public static List<TableInfo> getTables() {

        return tableInfos;
    }


    /**
     * 驼峰命名转下划线命名
     */
    public static String camelToSnake(String camelCaseStr) {
        if (camelCaseStr == null || camelCaseStr.isEmpty()) {
            return camelCaseStr;
        }

        StringBuilder result = new StringBuilder();
        result.append(Character.toLowerCase(camelCaseStr.charAt(0)));

        for (int i = 1; i < camelCaseStr.length(); i++) {
            char ch = camelCaseStr.charAt(i);
            if (Character.isUpperCase(ch)) {
                result.append('_').append(Character.toLowerCase(ch));
            } else {
                result.append(ch);
            }
        }

        return result.toString();
    }


}
